"""
利润率和价格变动记录管理模块
"""
from datetime import datetime, timedelta
from typing import List, Optional, Dict, Any, Tuple
from sqlalchemy import and_, or_, desc
from com.db.models import ProfitRatePriceRecord
from com.db.sqlalchemy_database import SQLAlchemyDatabaseManager
from com.utils.logger import get_logger

logger = get_logger("ProfitRatePriceManager")

class ProfitRatePriceManager:
    """利润率和价格变动记录管理器"""
    
    def __init__(self):
        """初始化利润率和价格变动记录管理器"""
        self.db = SQLAlchemyDatabaseManager()
    
    def add_record(self, record_data: Dict[str, Any]) -> int:
        """
        添加利润率和价格变动记录
        
        Args:
            record_data: 记录数据字典，包含以下字段：
                - goods_id: 商品ID
                - goods_name: 商品名称
                - hash_name: 饰品哈希名称（可选）
                - profit_rate: 利润率
                - buy_price: 扫货价格
                - target_sell_price: 目标售价
                - operator: 操作人（可选）
                - remark: 备注（可选）
                - modify_type: 修改方式（可选，1：自动调整，2：人为调整，默认为1）
                - pricing_type: 扫货定价类型（可选，0：按固定价，1：动态利润率，2：按固定利润率，默认为1）
            
        Returns:
            int: 新记录的ID，添加失败则返回0
        """
        try:
            # 设置更新时间为当前时间
            if 'update_time' not in record_data:
                record_data['update_time'] = datetime.now()
                
            # 添加记录
            record = ProfitRatePriceRecord(**record_data)
            record_id = self.db.add(record)
            
            logger.info(f"添加利润率和价格变动记录成功: 商品ID={record_data['goods_id']}, 利润率={record_data['profit_rate']}")
            return record_id
        except Exception as e:
            logger.error(f"添加利润率和价格变动记录失败: {str(e)}", exc_info=True)
            return 0
    
    def get_latest_record(self, goods_id: int) -> Optional[Dict[str, Any]]:
        """
        获取商品最新的利润率和价格变动记录
        
        Args:
            goods_id: 商品ID
            
        Returns:
            Optional[Dict]: 记录字典，如果不存在返回None
        """
        try:
            session, query = self.db.query_with_session(ProfitRatePriceRecord)
            try:
                record = query.filter(
                    ProfitRatePriceRecord.goods_id == goods_id
                ).order_by(ProfitRatePriceRecord.update_time.desc()).first()
                
                if record:
                    logger.info(f"找到商品 {goods_id} 的最新利润率记录，时间：{record.update_time}")
                    return self._convert_model_to_dict(record)
                logger.info(f"未找到商品 {goods_id} 的利润率记录")
                return None
            finally:
                session.close()
        except Exception as e:
            logger.error(f"获取最新利润率记录失败: {str(e)}")
            return None
    
    def get_history_records(self, goods_id: int, limit: int = 10) -> List[Dict[str, Any]]:
        """
        获取商品的历史利润率和价格变动记录
        
        Args:
            goods_id: 商品ID
            limit: 返回记录数量限制，默认为10条
            
        Returns:
            List[Dict]: 记录列表
        """
        try:
            session, query = self.db.query_with_session(ProfitRatePriceRecord)
            try:
                records = query.filter(
                    ProfitRatePriceRecord.goods_id == goods_id
                ).order_by(ProfitRatePriceRecord.update_time.desc()).limit(limit).all()
                
                return [self._convert_model_to_dict(record) for record in records]
            finally:
                session.close()
        except Exception as e:
            logger.error(f"获取利润率历史记录失败: {str(e)}")
            return []
    
    def get_records_by_date_range(self, start_date: datetime, end_date: datetime) -> List[Dict[str, Any]]:
        """
        获取指定日期范围内的利润率和价格变动记录
        
        Args:
            start_date: 开始日期
            end_date: 结束日期
            
        Returns:
            List[Dict]: 记录列表
        """
        try:
            session, query = self.db.query_with_session(ProfitRatePriceRecord)
            try:
                records = query.filter(
                    and_(
                        ProfitRatePriceRecord.update_time >= start_date,
                        ProfitRatePriceRecord.update_time <= end_date
                    )
                ).order_by(ProfitRatePriceRecord.update_time.desc()).all()
                
                return [self._convert_model_to_dict(record) for record in records]
            finally:
                session.close()
        except Exception as e:
            logger.error(f"获取日期范围内的利润率记录失败: {str(e)}")
            return []
    
    def get_goods_with_profit_rate_changes(self, days: int = 7, change_threshold: float = 0.05) -> List[Dict[str, Any]]:
        """
        获取指定时间段内利润率变化超过阈值的商品列表
        
        Args:
            days: 查询最近几天的数据，默认7天
            change_threshold: 利润率变化阈值，默认0.05（5%）
            
        Returns:
            List[Dict]: 符合条件的商品列表，包含商品ID、名称和利润率变化信息
        """
        try:
            session, query = self.db.query_with_session(ProfitRatePriceRecord)
            try:
                start_date = datetime.now() - timedelta(days=days)
                
                # 获取所有有记录的商品ID
                goods_ids_result = query.filter(
                    ProfitRatePriceRecord.update_time >= start_date
                ).with_entities(ProfitRatePriceRecord.goods_id).distinct().all()
                goods_ids = [item[0] for item in goods_ids_result]
                
                result = []
                for goods_id in goods_ids:
                    # 获取该商品在时间段内的所有记录
                    records = query.filter(
                        and_(
                            ProfitRatePriceRecord.goods_id == goods_id,
                            ProfitRatePriceRecord.update_time >= start_date
                        )
                    ).order_by(ProfitRatePriceRecord.update_time.asc()).all()
                    
                    if len(records) < 2:
                        continue
                    
                    # 计算最大利润率变化
                    max_change = 0
                    for i in range(1, len(records)):
                        change = abs(records[i].profit_rate - records[i-1].profit_rate)
                        if change > max_change:
                            max_change = change
                    
                    # 如果变化超过阈值，添加到结果中
                    if max_change >= change_threshold:
                        first_record = records[0]
                        last_record = records[-1]
                        result.append({
                            'goods_id': goods_id,
                            'goods_name': last_record.goods_name,
                            'hash_name': last_record.hash_name,
                            'first_profit_rate': first_record.profit_rate,
                            'last_profit_rate': last_record.profit_rate,
                            'profit_rate_change': last_record.profit_rate - first_record.profit_rate,
                            'first_buy_price': first_record.buy_price,
                            'last_buy_price': last_record.buy_price,
                            'first_target_price': first_record.target_sell_price,
                            'last_target_price': last_record.target_sell_price,
                            'first_update_time': first_record.update_time,
                            'last_update_time': last_record.update_time
                        })
                
                return result
            finally:
                session.close()
        except Exception as e:
            logger.error(f"获取利润率变化商品列表失败: {str(e)}")
            return []
    
    def delete_record(self, record_id: int) -> bool:
        """
        删除利润率和价格变动记录
        
        Args:
            record_id: 记录ID
            
        Returns:
            bool: 是否删除成功
        """
        try:
            record = self.db.get_by_id(ProfitRatePriceRecord, record_id)
            if not record:
                logger.warning(f"利润率记录不存在: ID={record_id}")
                return False
                
            self.db.delete_obj(record)
            logger.info(f"删除利润率记录成功: ID={record_id}")
            return True
        except Exception as e:
            logger.error(f"删除利润率记录失败: {str(e)}")
            return False
    
    def update_record(self, record_id: int, update_data: Dict[str, Any]) -> bool:
        """
        更新利润率和价格变动记录
        
        Args:
            record_id: 记录ID
            update_data: 更新数据字典
            
        Returns:
            bool: 是否更新成功
        """
        try:
            record = self.db.get_by_id(ProfitRatePriceRecord, record_id)
            if not record:
                logger.warning(f"利润率记录不存在: ID={record_id}")
                return False
            
            for key, value in update_data.items():
                if hasattr(record, key):
                    setattr(record, key, value)
            
            record.update_at = datetime.now()
            self.db.update_obj(record)
            logger.info(f"更新利润率记录成功: ID={record_id}")
            return True
        except Exception as e:
            logger.error(f"更新利润率记录失败: {str(e)}")
            return False
    
    def bulk_insert_records(self, records: List[Dict[str, Any]]) -> int:
        """
        批量插入利润率和价格变动记录
        
        Args:
            records: 记录数据字典列表，每个字典包含以下字段：
                - goods_id: 商品ID
                - goods_name: 商品名称
                - hash_name: 饰品哈希名称（可选）
                - profit_rate: 利润率
                - buy_price: 扫货价格
                - target_sell_price: 目标售价
                - operator: 操作人（可选）
                - remark: 备注（可选）
                - modify_type: 修改方式（可选，1：自动调整，2：人为调整，默认为1）
                - pricing_type: 扫货定价类型（可选，0：按固定价，1：动态利润率，2：按固定利润率，默认为1）
            
        Returns:
            int: 成功插入的记录数量
        """
        try:
            session = self.db.get_session()
            try:
                record_objects = []
                
                for record_data in records:
                    if 'update_time' not in record_data:
                        record_data['update_time'] = datetime.now()
                    record = ProfitRatePriceRecord(**record_data)
                    record_objects.append(record)
                
                session.add_all(record_objects)
                session.commit()
                
                logger.info(f"批量添加利润率记录成功: {len(records)}条")
                return len(records)
            except Exception as e:
                session.rollback()
                raise e
            finally:
                session.close()
        except Exception as e:
            logger.error(f"批量添加利润率记录失败: {str(e)}")
            return 0
    
    def _convert_model_to_dict(self, record: ProfitRatePriceRecord) -> Dict[str, Any]:
        """将模型对象转换为字典"""
        if not record:
            return None
            
        result = {
            'id': record.id,
            'goods_id': record.goods_id,
            'goods_name': record.goods_name,
            'hash_name': record.hash_name,
            'profit_rate': record.profit_rate,
            'buy_price': record.buy_price,
            'target_sell_price': record.target_sell_price,
            'update_time': record.update_time,
            'operator': record.operator,
            'remark': record.remark,
            'modify_type': record.modify_type,
            'pricing_type': record.pricing_type,
            'create_at': record.create_at,
            'update_at': record.update_at,
            
            # 基础价格字段
            'base_snipe_price': record.base_snipe_price,
            'common_sell_price': record.common_sell_price,
            'normal_price': record.normal_price,
            'adjustment_factor': record.adjustment_factor,
            'price_variance': record.price_variance,
            'upper_bound_price': record.upper_bound_price,
            'lower_bound_price': record.lower_bound_price,
            
            # 动态价格调整字段
            'min_required_gap': record.min_required_gap,
            'actual_price_gap_ratio': record.actual_price_gap_ratio,
            'history_data_count': record.history_data_count,
            'reference_price_source': record.reference_price_source,
            'adjustment_strategy': record.adjustment_strategy,
            'price_change_percentage': record.price_change_percentage,
            'estimated_profit_rate': record.estimated_profit_rate,
            
            # 价格趋势信息
            'price_trend_direction': record.price_trend_direction,
            'price_trend_slope': record.price_trend_slope,
            'price_trend_strength': record.price_trend_strength,
            
            # 异常值过滤信息
            'outlier_filter_applied': record.outlier_filter_applied,
            'outlier_count': record.outlier_count,
            
            # 参考价格选择逻辑
            'reference_price_selection_method': record.reference_price_selection_method,
            'reference_price_percentile': record.reference_price_percentile,
            
            # 价格间距判断详情
            'price_gap_absolute': record.price_gap_absolute,
            'gap_check_result': record.gap_check_result,
            
            # 最小参考价格信息
            'min_reference_price': record.min_reference_price,
            'min_reference_price_source': record.min_reference_price_source,
            
            # 历史数据统计信息
            'history_data_time_span': record.history_data_time_span,
            'history_data_first_date': record.history_data_first_date,
            'history_data_last_date': record.history_data_last_date,
            
            # 决策路径跟踪
            'decision_path': record.decision_path
        }
        
        # 过滤掉None值，避免返回大量空字段
        return {k: v for k, v in result.items() if v is not None}
